#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
-- 建库
create database if not exists edu_dws;


--======================客户意向日统计宽表=================================--
drop table IF EXISTS edu_dws.dws_customer_relationship_daycout;
create table IF NOT EXISTS edu_dws.dws_customer_relationship_daycout(
    --维度字段
    area string COMMENT '地区',
    depart_id int COMMENT '部门id',
    depart_name string COMMENT '部门名称',
    clue_state string COMMENT '新老学员',
    origin_type string COMMENT '线上线下，线上(NETSERVICE or PRESIGNUP)',
    sub_id int COMMENT '学科id',
    sub_name string COMMENT '学科名称',
    school_id int COMMENT '校区id',
    school_name string COMMENT '校区名称',
    origin_channel STRING COMMENT '来源渠道',
    year_code string COMMENT '年',
    year_month string COMMENT '年月',
    year_month_day string COMMENT '年日',

    group_type string COMMENT '分组类型:area,depart,clue_state,origin_channel,sub,school,all',

    --指标
    id_count bigint COMMENT '意向用户个数'
)
COMMENT '客户意向日统计宽表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE;

--======================客户线索日统计宽表=================================--
drop table if exists edu_dws.dws_customer_clue_daycout;
create table if not exists edu_dws.dws_customer_clue_daycout(
    --维度
    clue_state string COMMENT '新老学员',
    origin_type string COMMENT '线上线下，线上(NETSERVICE or PRESIGNUP)',
    appeal_status TINYINT COMMENT '申诉状态，0:待稽核 1:无效 2：有效',
    datetime string COMMENT '小时',

    group_type string COMMENT '分组类型:datetime,dt',
    --指标
    first_id_effective_count bigint COMMENT '有效线索个数',
    first_id_count bigint  COMMENT '总线索个数'
)
COMMENT '客户线索日统计宽表'
PARTITIONED BY (dt string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '/t'
STORED AS ORC TBLPROPERTIES ('orc.compress' = 'SNAPPY');
"